﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using NPOI.HSSF.UserModel;

namespace CommunitySystem.BO.EmpbasicBO
{
    public class EmpbasicBO
    {
        public string GetDengLuJueSe(string loginName)
        {
            string sql = string.Format(@"select RoleName from Roles where ID = 
(select RoleID from UserInfo where LoginName = '{0}')", loginName);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0]["RoleName"].ToString();
        }

        public string GetDengLuOrg(string loginName)
        {
            string sql = string.Format(@"select OrgCode from UserInfo where LoginName = '{0}'", loginName);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0]["OrgCode"].ToString();
        }

        #region 员工基本信息
        /// <summary>
        /// 查询总共多少条数据yy
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="EmpID"></param>
        /// <param name="name"></param>
        /// <param name="orgtype"></param>
        /// <param name="highQueryCondition">高级查询条件，xf修改</param>
        /// <returns></returns>
        public int GetEmp_BasicInfoCounts(string OrgID, string EmpID, string name, string orgtype, string empstatus, string highQueryCondition)
        {
            string sql = "";
            //用于查看员工列表
            if (EmpID == "0")
                //用于列表上面的搜索
                if (string.IsNullOrEmpty(name))
                {
                    sql = @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 

{0}
where EmpStatus = '" + empstatus + @"' and 1=1 ";
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        sql += " and (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) ";
                    }
                    if (!string.IsNullOrEmpty(orgtype) && orgtype != "组织机构")
                    {
                        sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 
{0}
where EmpStatus = '" + empstatus + @"' and 1=1 
  and o.Type = '{0}'", orgtype);
                    }
                }
                //姓名输入框有文字
                else
                {
                    sql =
                        @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 
--left join OrgForHR o on emp.orgid=o.ID 
{0}
where  EmpStatus = '" + empstatus + @"' and 1=1 and  emp.Name like '%" +
                        name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%'";
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        sql += "  and (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) ";
                    }
                    if (!string.IsNullOrEmpty(orgtype) && orgtype != "组织机构")
                    {
                        sql =
                            @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 

{0}
where  EmpStatus = '" + empstatus + @"' and 1=1 and  emp.Name like '%" +
                            name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%'" + " and o.Type = '" + orgtype + "'";
                    }
                }

            //用于查看单个员工
            else sql = @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 
{0}
where emp.ID in  EmpID";
            if (!string.IsNullOrEmpty(highQueryCondition))
            {
                string condition = @"
inner join OrgForHR o on emp.orgid=o.ID 
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID";
                sql = string.Format(sql, condition);
                sql += " and " + highQueryCondition;
            }
            else
            {
                sql = string.Format(sql, " inner join OrgForHR o on emp.orgid=o.ID   ");
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }


        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="EmpID"></param>
        /// <param name="name"></param>
        /// <param name="orgtype"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="highQueryCondition">高级查询条件，xf修改</param>
        /// <returns></returns>
        public ObservableCollection<Emp_BasicInfoModel> GetEmp_BasicInfos(string OrgID, string EmpID, string name, string orgtype, int start, int end, string empstatus, string highQueryCondition)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = "";
            //用于查看员工列表
            if (EmpID == "0")
                //用于列表上面的搜索
                if (string.IsNullOrEmpty(name))
                {
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        sql = @"SELECT top " + (end - start) + @" row_number()over(order by a.ID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
           ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
      ,JuTuiXiuDate
      ,[BirthPlace]
      ,NativePlace
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
      ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation
  FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
        left join (select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
  left join
        (
          select emp1.ID,EducationName,EducationType,GraduationDate,School,Professional,IsTopEducation from Emp_BasicInfo emp1 
                left join
                (
                select * from Emp_Education where IsTopEducation = 1
                ) ed1 on emp1.ID = ed1.EmpBasicID
        ) ee on ee.ID = emp.ID 
where EmpStatus = '" + empstatus + @"' and  emp.ID not   in   
    (select   top " + start + @" emp.ID from   Emp_BasicInfo emp left join OrgForHR o on o.ID = emp.OrgID
        where  EmpStatus = '" + empstatus + @"' and (ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")))  and  (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) and 1=1 {0} ) a ";
                    }
                    else
                    {
                        sql = @"SELECT top " + (end - start) + @" row_number()over(order by a.ID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
     ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
      ,JuTuiXiuDate
      ,[BirthPlace]
      ,NativePlace
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation 
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID

left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp1.ID,EducationName,EducationType,GraduationDate,School,Professional,IsTopEducation from Emp_BasicInfo emp1 left join
(select * from Emp_Education where IsTopEducation = 1) ed1 on emp1.ID = ed1.EmpBasicID) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' and emp.ID not   in  
(select   top " + start + @" emp.ID from   Emp_BasicInfo emp left join OrgForHR o on o.ID = emp.OrgID where EmpStatus = '" + empstatus + @"')  and 1=1 {0}) a ";
                    }

                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    //                    if (!string.IsNullOrEmpty(orgtype) && orgtype != "组织机构")
                    //                    {
                    //                        sql = string.Format(@"SELECT top " + (end - start) + @"  row_number()over(order by emp.ID ) Sequence, emp.[ID]
                    //      ,emp.[Name]
                    //      ,[CardNo]
                    //      ,[Gender]
                    //      ,[BirthPlace]
                    //      ,[HouseholdRegister]
                    //      ,[Native]
                    //      ,[Political]
                    //      ,[Health]
                    //      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
                    //      ,[RegisterNo]
                    //      ,[OrganizationType]
                    //      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
                    //      ,[NativePlace]
                    //      ,[Marriage]
                    //      ,[HomeAddress]
                    //      ,[Phone]
                    //      ,[EmpIdentity]
                    //      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
                    //      ,[Special]
                    //      ,[TechnicalPositions]
                    //      ,[PersonnelType]
                    //      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
                    //      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
                    //        ,OrgID,o.Name OrgName
                    //        ,PositionName,'false' as IsSelected
                    //  FROM Emp_BasicInfo emp inner join OrgForHR o on emp.orgid=o.ID  where  emp.ID not   in   
                    //(select   top " + start + @" ID from   Emp_BasicInfo ) and 1=1 
                    //  and o.Type = '{0}'", orgtype);
                    //                    }
                }
                //姓名输入框有文字
                else
                {
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        sql =
                            @"SELECT top " + (end - start) + @" row_number()over(order by a.ID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
      ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
      ,JuTuiXiuDate
      ,[BirthPlace]
      ,NativePlace
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
       ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation  
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp1.ID,EducationName,EducationType,GraduationDate,School,Professional,IsTopEducation from Emp_BasicInfo emp1 left join
(select * from Emp_Education where IsTopEducation = 1) ed1 on emp1.ID = ed1.EmpBasicID) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' and emp.ID not   in    
(select   top " +
                            start + @" emp.ID from   Emp_BasicInfo emp left join OrgForHR o on o.ID = emp.OrgID
 where EmpStatus = '" + empstatus + @"' and (ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) ) and 1=1  and  (emp.Name like '%" +
                            name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%') and  (o.ParentID in (" +
                            OrgID + ") or o.ID in (" + OrgID + ")) and 1=1  {0}) a ";
                    }
                    else
                    {
                        sql =
                            @"SELECT top " + (end - start) + @" row_number()over(order by a.ID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
     ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
      ,JuTuiXiuDate
      ,[BirthPlace]
      ,NativePlace
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation  
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
left join
(select emp1.ID,EducationName,EducationType,GraduationDate,School,Professional,IsTopEducation from Emp_BasicInfo emp1 left join
(select * from Emp_Education where IsTopEducation = 1) ed1 on emp1.ID = ed1.EmpBasicID) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' and emp.ID not   in   
(select   top " +
                            start + @" emp.ID from   Emp_BasicInfo emp left join OrgForHR o on o.ID = emp.OrgID where EmpStatus = '" + empstatus + @"') and 1=1  and  emp.Name like '%" +
                            name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%' and 1=1 {0}) a ";
                    }
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    //                    if (!string.IsNullOrEmpty(orgtype) && orgtype != "组织机构")
                    //                    {
                    //                        sql =
                    //                            @"SELECT top " + (end - start) + @"   row_number()over(order by emp.ID ) Sequence, emp.[ID]
                    //      ,emp.[Name]
                    //      ,[CardNo]
                    //      ,[Gender]
                    //      ,[BirthPlace]
                    //      ,[HouseholdRegister]
                    //      ,[Native]
                    //      ,[Political]
                    //      ,[Health]
                    //      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
                    //      ,[RegisterNo]
                    //      ,[OrganizationType]
                    //      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
                    //      ,[NativePlace]
                    //      ,[Marriage]
                    //      ,[HomeAddress]
                    //      ,[Phone]
                    //      ,[EmpIdentity]
                    //      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
                    //      ,[Special]
                    //      ,[TechnicalPositions]
                    //      ,[PersonnelType]
                    //      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
                    //      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
                    //        ,OrgID,o.Name OrgName
                    //        ,PositionName,'false' as IsSelected
                    //  FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID where  emp.ID not   in   
                    //(select   top " + start + @" ID from   Emp_BasicInfo ) and 1=1 and  emp.Name like '%" +
                    //                            name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%'" + " and o.Type = '" + orgtype + "'";
                    //                    }
                }

            //用于查看单个员工
            else
            {
                sql = string.Format(@"SELECT top " + (end - start) + @" row_number()over(order by emp.ID ) Sequence, emp.[ID]
      ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
      ,JuTuiXiuDate
      ,[BirthPlace]
      ,NativePlace
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special],emp.EmpStatus
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation  
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ,JobStatus from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
  left join Emp_Education ee on ee.EmpBasicID = emp.ID where emp.ID in {0} and EmpStatus = '" + empstatus + @"'", EmpID);
                sql += "  {0}";
            }
            if (!string.IsNullOrEmpty(highQueryCondition))
                sql = string.Format(sql, " and  " + highQueryCondition);
            else
                sql = string.Format(sql, " and 1=1");
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;
        }

        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <returns></returns>
        public ObservableCollection<Emp_BasicInfoModel> GetDanWeiEmp_BasicInfos(string OrgID, string empstatus)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = @"SELECT row_number()over(order by emp.ID ) Sequence, emp.[ID]
      ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,[BirthPlace]
      ,[HouseholdRegister],
         ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else TuiXiuDate end TuiXiuDate,
JuTuiXiuDate,HuoDeFangShi
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,[NativePlace]
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
        ,OrgID,o.Name OrgName,case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
        ,PositionName,'false' as IsSelected,(CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) as TuiXiuDateString 
  FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID where EmpStatus = '" + empstatus + @"' and  1=1 ";
            if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
            {
                sql += " and  (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) ";
            }
            sql += " order by emp.[Name]";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;

        }
        public int AddEmp_BasicInfo(Emp_BasicInfoModel item)
        {
            string yanzhengsql = string.Format(@"select * from Emp_BasicInfo where name = '{0}' and cardno = '{1}'",
                                               item.Name, item.CardNo);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(yanzhengsql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return -1;
            }
            string sql = string.Format(@"INSERT INTO Emp_BasicInfo
           ([Name]
           ,[CardNo]
           ,[Gender]
           ,[BirthPlace]
           ,[HouseholdRegister]
           ,[Native]
           ,[Political]
           ,[Health]
           ,[WorkDate]
           ,[RegisterNo]
           ,[OrganizationType]
           ,[NowWorkDate]
           ,[NativePlace]
           ,[Marriage]
           ,[HomeAddress]
           ,[Phone]
           ,[EmpIdentity]
           ,[PartyTime]
           ,[Special]
           ,[TechnicalPositions]
           ,[PersonnelType]
           ,[RegisterDate]
            ,[Birthday]
,OrgID
        ,PositionName,TuiXiuDate,JuTuiXiuDate,HuoDeFangShi,EmpStatus,StatusDate)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}'
           ,'{20}'
           ,'{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}');SELECT @@IDENTITY", item.Name, item.CardNo, item.Gender, item.BirthPlace, item.HouseholdRegister, item.Native, item.Political, item.Health,
                    item.WorkDate, item.RegisterNo, item.OrganizationType, item.NowWorkDate, item.NativePlace, item.Marriage, item.HomeAddress,
                    item.Phone, item.EmpIdentity, item.PartyTime, item.Special, item.TechnicalPositions, item.PersonnelType, item.RegisterDate, item.Birthday,
                    item.OrgID, item.PositionName, item.TuiXiuDate, item.JuTuiXiuDate, item.HuoDeFangShi, item.EmpStatus, item.StatusDate);
            
            ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            InsertCheGai(Convert.ToInt32(ds.Tables[0].Rows[0][0]),"新增人员","登记类");
            return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        

        public bool UpdateEmp_BasicInfo(Emp_BasicInfoModel item)
        {
            string sql = string.Format(@"UPDATE Emp_BasicInfo
   SET [Name] = '{0}'
      ,[CardNo] = '{1}'
      ,[Gender] = '{2}'
      ,[BirthPlace] = '{3}'
      ,[HouseholdRegister] = '{4}'
      ,[Native] = '{5}'
      ,[Political] = '{6}'
      ,[Health] = '{7}'
      ,[WorkDate] = '{8}'
      ,[RegisterNo] = '{9}'
      ,[OrganizationType] = '{10}'
      ,[NowWorkDate] = '{11}'
      ,[NativePlace] = '{12}'
      ,[Marriage] = '{13}'
      ,[HomeAddress] = '{14}'
      ,[Phone] = '{15}'
      ,[EmpIdentity] = '{16}'
      ,[PartyTime] = '{17}'
      ,[Special] = '{18}'
      ,[TechnicalPositions] = '{19}'
      ,[PersonnelType] = '{20}'
      ,[RegisterDate] = '{21}'
        ,[Birthday] = '{22}'
,OrgID= '{23}'
        ,PositionName= '{24}'
,[TuiXiuDate] = '{25}'
,JuTuiXiuDate= '{26}'
        ,HuoDeFangShi= '{27}',empstatus= '{28}',StatusDate = '{29}'
 WHERE ID = {30}", item.Name, item.CardNo, item.Gender, item.BirthPlace, item.HouseholdRegister, item.Native, item.Political, item.Health,
                    item.WorkDate, item.RegisterNo, item.OrganizationType, item.NowWorkDate, item.NativePlace, item.Marriage, item.HomeAddress,
                    item.Phone, item.EmpIdentity, item.PartyTime, item.Special, item.TechnicalPositions, item.PersonnelType, item.RegisterDate, item.Birthday
                    , item.OrgID, item.PositionName, item.TuiXiuDate, item.JuTuiXiuDate, item.HuoDeFangShi, item.EmpStatus, item.StatusDate, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_BasicInfo(string ID)
        {
            string sql = @"delete from Emp_Assessment where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Degree where EmpbasicID in  " + ID + ";" +
                                       "delete from Emp_Duty where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Education where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_FamilyRelation where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Punishment where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Resume where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_RewardInfo where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_BasicInfo where ID in  " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 职务信息
        public ObservableCollection<Emp_Duty> GetEmp_Dutys(string EmpID, int ID)
        {
            ObservableCollection<Emp_Duty> rights = new ObservableCollection<Emp_Duty>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DutyNo]
      ,[DutyName]
      ,[DutyLevel]
      ,[DutyAttribute]
      ,case when [OKDate] = '1900-01-01 00:00:00.000' then null else [OKDate] end [OKDate]
      ,[OrganizationName]
      ,[Sec]
,case when [BRZDQRQ] = '1900-01-01 00:00:00.000' then null else [BRZDQRQ] end [BRZDQRQ]
      ,[WorkCharge]
      ,[ServiceMode]
      ,[IsExceptionalPromotion]
      ,[JobStatus],RenQi,RZSYQ,LianRen
      ,case when [JobDate]= '1900-01-01 00:00:00.000' then null else [JobDate] end [JobDate]
,case when [GRStartDate]= '1900-01-01 00:00:00.000' then null else [GRStartDate] end [GRStartDate]
,case when [GREndDate]= '1900-01-01 00:00:00.000' then null else [GREndDate] end [GREndDate]
,case when [RXRZWSXSJ]= '1900-01-01 00:00:00.000' then null else [RXRZWSXSJ] end [RXRZWSXSJ]
,emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Duty
ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where EmpbasicID in {0} order by EmpbasicID, OKDate", EmpID);
            else
            {
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DutyNo]
      ,[DutyName]
      ,[DutyLevel]
      ,[DutyAttribute],RenQi,RZSYQ,LianRen
,case when [BRZDQRQ] = '1900-01-01 00:00:00.000' then null else [BRZDQRQ] end [BRZDQRQ]
      ,case when [OKDate] = '1900-01-01 00:00:00.000' then null else [OKDate] end [OKDate]
      ,[OrganizationName]
      ,[Sec]
      ,[WorkCharge]
      ,[ServiceMode]
      ,[IsExceptionalPromotion]
      ,[JobStatus],emp.Name EmpName,emp.CardNo EmpNo
      ,case when [JobDate]= '1900-01-01 00:00:00.000' then null else [JobDate] end [JobDate]
,case when [GRStartDate]= '1900-01-01 00:00:00.000' then null else [GRStartDate] end [GRStartDate]
,case when [GREndDate]= '1900-01-01 00:00:00.000' then null else [GREndDate] end [GREndDate]
,case when [RXRZWSXSJ]= '1900-01-01 00:00:00.000' then null else [RXRZWSXSJ] end [RXRZWSXSJ]
  FROM Emp_Duty ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} order by OKDate", ID);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_Duty.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_Duty(Emp_Duty item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[DutyLevel]
           ,[DutyAttribute]
           ,[OKDate]
           ,[OrganizationName]
           ,[Sec]
           ,[WorkCharge]
           ,[ServiceMode]
           ,[IsExceptionalPromotion]
           ,[JobStatus]
           ,[JobDate],RenQi,RZSYQ,LianRen,BRZDQRQ,RXRZWSXSJ,GRStartDate,GREndDate)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}','{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}','{17}','{18}','{19}')", item.EmpBasicID, item.DutyNo, item.DutyName, item.DutyLevel, item.DutyAttribute, item.OKDate, item.OrganizationName, item.Sec,
                    item.WorkCharge, item.ServiceMode, item.IsExceptionalPromotion, item.JobStatus, item.JobDate, item.RenQi, item.RZSYQ, item.LianRen, item.BRZDQRQ, item.RXRZWSXSJ, item.GRStartDate, item.GREndDate);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_Duty(Emp_Duty item)
        {
            string sql = string.Format(@"UPDATE Emp_Duty
   SET [EmpbasicID] = '{0}'
      ,[DutyNo] = '{1}'
      ,[DutyName] = '{2}'
      ,[DutyLevel] = '{3}'
      ,[DutyAttribute] = '{4}'
      ,[OKDate] = '{5}'
      ,[OrganizationName] = '{6}'
      ,[Sec] = '{7}'
      ,[WorkCharge] = '{8}'
      ,[ServiceMode] = '{9}'
      ,[IsExceptionalPromotion] = '{10}'
      ,[JobStatus] = '{11}'
      ,[JobDate] = '{12}',RenQi= '{13}',RZSYQ= '{14}',LianRen= '{15}',BRZDQRQ= '{16}',RXRZWSXSJ = '{17}',GRStartDate= '{18}',GREndDate = '{19}'
 WHERE ID = {20}", item.EmpBasicID, item.DutyNo, item.DutyName, item.DutyLevel, item.DutyAttribute, item.OKDate, item.OrganizationName, item.Sec,
                    item.WorkCharge, item.ServiceMode, item.IsExceptionalPromotion, item.JobStatus, item.JobDate, item.RenQi, item.RZSYQ, item.LianRen
                    , item.BRZDQRQ, item.RXRZWSXSJ, item.GRStartDate, item.GREndDate, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_Duty(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Duty where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 简历信息
        public ObservableCollection<Emp_ResumeModel> GetEmp_Resume(string EmpID, int ID)
        {
            ObservableCollection<Emp_ResumeModel> rights = new ObservableCollection<Emp_ResumeModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[BeginDate]
      ,ed.[EndDate]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Witness]
      ,ed.[IsPeriod]
      ,case when [IsPeriod] = 0 then '否' else '是' end IsPeriodString,emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Resume ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by EmpbasicID, BeginDate", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[BeginDate]
      ,[EndDate]
      ,[Unit]
      ,[Duty]
      ,[Witness]
      ,[IsPeriod],emp.Name EmpName,emp.CardNo EmpNo
,case when [IsPeriod] = 0 then '否' else '是' end IsPeriodString
  FROM Emp_Resume ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} order by BeginDate", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_ResumeModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_Resume(Emp_ResumeModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Resume
           ([EmpbasicID]
           ,[BeginDate]
           ,[EndDate]
           ,[Unit]
           ,[Duty]
           ,[Witness]
           ,[IsPeriod])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.BeginDate, item.EndDate, item.Unit, item.Duty, item.Witness, item.IsPeriod);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_ResumeModel(Emp_ResumeModel item)
        {
            string sql = string.Format(@"update Emp_Resume
   SET [EmpbasicID] = {0}
      ,[BeginDate] = '{1}'
      ,[EndDate] = '{2}'
      ,[Unit] = '{3}'
      ,[Duty] = '{4}'
      ,[Witness] = '{5}'
      ,[IsPeriod] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.BeginDate, item.EndDate, item.Unit, item.Duty, item.Witness, item.IsPeriod, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_ResumeModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Resume where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 学历信息
        public ObservableCollection<Emp_EducationModel> GetEmp_EducationModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_EducationModel> rights = new ObservableCollection<Emp_EducationModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpBasicID]
      ,ed.[EducationName]
      ,ed.[EducationType]
      ,ed.[EducationaSystem]
      ,ed.[GraduationDate]
      ,ed.[School]
      ,ed.[Professional]
      ,ed.[IsTopEducation]
,case when [IsTopEducation] = 0 then '否' else '是' end IsTopEducationString,emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Education ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpBasicID]
      ,ed.[EducationName]
      ,ed.[EducationType]
      ,ed.[EducationaSystem]
      ,ed.[GraduationDate]
      ,ed.[School]
      ,ed.[Professional]
      ,ed.[IsTopEducation],emp.Name EmpName,emp.CardNo EmpNo
,case when ed.[IsTopEducation] = 0 then '否' else '是' end IsTopEducationString
  FROM Emp_Education ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_EducationModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_EducationModel(Emp_EducationModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Education
           ([EmpBasicID]
           ,[EducationName]
           ,[EducationType]
           ,[EducationaSystem]
           ,[GraduationDate]
           ,[School]
           ,[Professional]
           ,[IsTopEducation])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}')", item.EmpBasicID, item.EducationName, item.EducationType, item.EducationaSystem, item.GraduationDate, item.School, item.Professional, item.IsTopEducation);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_EducationModel(Emp_EducationModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Education
   SET [EmpBasicID] = {0}
      ,[EducationName] = '{1}'
      ,[EducationType] = '{2}'
      ,[EducationaSystem] = '{3}'
      ,[GraduationDate] = '{4}'
      ,[School] = '{5}'
      ,[Professional] = '{6}'
      ,[IsTopEducation] = '{7}'
 WHERE ID = {8}", item.EmpBasicID, item.EducationName, item.EducationType, item.EducationaSystem, item.GraduationDate, item.School, item.Professional, item.IsTopEducation, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_EducationModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Education where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 学位信息
        public ObservableCollection<Emp_DegreeModel> GetEmp_DegreeModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_DegreeModel> rights = new ObservableCollection<Emp_DegreeModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[DegreeName]
      ,ed.[LearningType]
      ,ed.[EducationalSystem]
      ,ed.[EducationDate]
      ,ed.[School]
      ,ed.[Professional],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Degree ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DegreeName]
      ,[LearningType]
      ,[EducationalSystem]
      ,[EducationDate]
      ,[School]
      ,[Professional],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Degree ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_DegreeModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_DegreeModel(Emp_DegreeModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Degree
           ([EmpbasicID]
           ,[DegreeName]
           ,[LearningType]
           ,[EducationalSystem]
           ,[EducationDate]
           ,[School]
           ,[Professional])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.DegreeName, item.LearningType, item.EducationalSystem, item.EducationDate, item.School, item.Professional);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_DegreeModel(Emp_DegreeModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Degree
   SET [EmpbasicID] = {0}
      ,[DegreeName] = '{1}'
      ,[LearningType] = '{2}'
      ,[EducationalSystem] = '{3}'
      ,[EducationDate] = '{4}'
      ,[School] = '{5}'
      ,[Professional] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.DegreeName, item.LearningType, item.EducationalSystem, item.EducationDate, item.School, item.Professional, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_DegreeModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Degree where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 考核信息
        public ObservableCollection<Emp_AssessmentModel> GetEmp_AssessmentModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_AssessmentModel> rights = new ObservableCollection<Emp_AssessmentModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.ID
      ,ed.[EmpbasicID]
      ,ed.[AssessmentType]
      ,ed.[AssessmentResult]
      ,ed.[AssessmentDate],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Assessment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpbasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[AssessmentType]
      ,ed.[AssessmentResult]
      ,ed.[AssessmentDate],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Assessment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} ", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_AssessmentModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_AssessmentModel(Emp_AssessmentModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Assessment
           ([EmpbasicID]
           ,[AssessmentType]
           ,[AssessmentResult]
           ,[AssessmentDate])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}')", item.EmpBasicID, item.AssessmentType, item.AssessmentResult, item.AssessmentDate);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_AssessmentModel(Emp_AssessmentModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Assessment
   SET [EmpbasicID] = {0}
      ,[AssessmentType] = '{1}'
      ,[AssessmentResult] = '{2}'
      ,[AssessmentDate] = '{3}'
 WHERE ID = {4}", item.EmpBasicID, item.AssessmentType, item.AssessmentResult, item.AssessmentDate, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_AssessmentModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Assessment where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 奖惩信息
        public ObservableCollection<Emp_RewardInfoModel> GetEmp_RewardInfoModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_RewardInfoModel> rights = new ObservableCollection<Emp_RewardInfoModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[RewardNo]
      ,ed.[RewardName]
      ,ed.[RewardReason]
      ,ed.[OtherRewardReason]
      ,ed.[OKDate]
      ,ed.[OKUnit],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_RewardInfo ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[RewardNo]
      ,ed.[RewardName]
      ,ed.[RewardReason]
      ,ed.[OtherRewardReason]
      ,ed.[OKDate]
      ,ed.[OKUnit],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_RewardInfo ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_RewardInfoModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_RewardInfoModel(Emp_RewardInfoModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_RewardInfo
           ([EmpbasicID]
           ,[RewardNo]
           ,[RewardName]
           ,[RewardReason]
           ,[OtherRewardReason]
           ,[OKDate]
           ,[OKUnit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.RewardNo, item.RewardName, item.RewardReason, item.OtherRewardReason, item.OKDate, item.OKUnit);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_RewardInfoModel(Emp_RewardInfoModel item)
        {
            string sql = string.Format(@"UPDATE Emp_RewardInfo
   SET [EmpbasicID] = {0}
      ,[RewardNo] = '{1}'
      ,[RewardName] = '{2}'
      ,[RewardReason] ='{3}'
      ,[OtherRewardReason] = '{4}'
      ,[OKDate] = '{5}'
      ,[OKUnit] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.RewardNo, item.RewardName, item.RewardReason, item.OtherRewardReason, item.OKDate, item.OKUnit, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_RewardInfoModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_RewardInfo where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 处分信息
        public ObservableCollection<Emp_PunishmentModel> GetEmp_PunishmentModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_PunishmentModel> rights = new ObservableCollection<Emp_PunishmentModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[PunishmentNo]
      ,ed.[PunishmentName]
      ,ed.[PunishmentReason]
      ,ed.[OkUnit]
      ,ed.[OKDate]
      ,ed.[PunishmentTimeLimit],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Punishment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[PunishmentNo]
      ,[PunishmentName]
      ,[PunishmentReason]
      ,[OkUnit]
      ,[OKDate]
      ,[PunishmentTimeLimit],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_Punishment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_PunishmentModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_PunishmentModel(Emp_PunishmentModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Punishment
           ([EmpbasicID]
           ,[PunishmentNo]
           ,[PunishmentName]
           ,[PunishmentReason]
           ,[OkUnit]
           ,[OKDate]
           ,[PunishmentTimeLimit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.PunishmentNo, item.PunishmentName, item.PunishmentReason, item.OkUnit, item.OKDate, item.PunishmentTimeLimit);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_PunishmentModel(Emp_PunishmentModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Punishment
   SET [EmpbasicID] = {0}
      ,[PunishmentNo] = '{1}'
      ,[PunishmentName] = '{2}'
      ,[PunishmentReason] = '{3}'
      ,[OkUnit] = '{4}'
      ,[OKDate] = '{5}'
      ,[PunishmentTimeLimit] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.PunishmentNo, item.PunishmentName, item.PunishmentReason, item.OkUnit, item.OKDate, item.PunishmentTimeLimit, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_PunishmentModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Punishment where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 家庭信息
        public ObservableCollection<Emp_FamilyRelationModel> GetEmp_FamilyRelationModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_FamilyRelationModel> rights = new ObservableCollection<Emp_FamilyRelationModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[Name]
      ,ed.[Appellation]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Political]
      ,ed.[Birthday],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_FamilyRelation ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[Name]
      ,ed.[Appellation]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Political]
      ,ed.[Birthday],emp.Name EmpName,emp.CardNo EmpNo
  FROM Emp_FamilyRelation ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_FamilyRelationModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_FamilyRelationModel(Emp_FamilyRelationModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_FamilyRelation
           ([EmpbasicID]
           ,[Name]
           ,[Appellation]
           ,[Unit]
           ,[Duty]
           ,[Political]
           ,[Birthday])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.Name, item.Appellation, item.Unit, item.Duty, item.Political, item.Birthday);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_FamilyRelationModel(Emp_FamilyRelationModel item)
        {
            string sql = string.Format(@"UPDATE Emp_FamilyRelation
   SET [EmpbasicID] = {0}
      ,[Name] = '{1}'
      ,[Appellation] = '{2}'
      ,[Unit] ='{3}'
      ,[Duty] = '{4}'
      ,[Political] = '{5}'
      ,[Birthday] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.Name, item.Appellation, item.Unit, item.Duty, item.Political, item.Birthday, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_FamilyRelationModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_FamilyRelation where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 操作日志
        public int GetSystemMarkCounts(string dengluren, string caozuoren, string caozuoriqi)
        {
            string sql = "";
            //用于查看员工列表
            //            if (GetDengLuJueSe(dengluren) != "管理员" )
            //                sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
            //  FROM system_mark emp  where Operator = '{0}' and 1=1", dengluren);
            //            else
            sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM system_mark emp where 1=1 ");
            if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),UpdateDate,111) = convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }
        public ObservableCollection<System_MarkManageModel> GetSystemMark(string dengluren, int start, int end, string caozuoren, string caozuoriqi)
        {
            ObservableCollection<System_MarkManageModel> rights = new ObservableCollection<System_MarkManageModel>();
            string sql = "";
            //            if(dengluren != "admin")
            //                sql = string.Format(@"SELECT top " + (end - start) + @" s.[ID]
            //      ,[UpdateDate]
            //      ,[InvolvingSubset]
            //      ,[Content]
            //      ,[InvolvingPersonID]
            //      ,[Operator],[OPerationStyle]
            //      ,[InvolvingColumn],OldContent,emp.Name EmpName
            //  FROM System_Mark s left join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where Operator = '{0}' and '{1}' and '{2}' and
            // s.ID not   in   
            //    (select   top " + start + @" s.ID from   System_Mark s inner join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where Operator = '{0}' and '{1}' and '{2}' order by UpdateDate desc)", dengluren);
            //            else
            sql =
                string.Format(@"SELECT top " + (end - start) +
                              @" s.[ID]
      ,[UpdateDate]
      ,[InvolvingSubset]
      ,[Content]
      ,[InvolvingPersonID]
      ,[Operator],[OPerationStyle]
      ,[InvolvingColumn],OldContent,emp.Name EmpName
  FROM System_Mark s left join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where 1=1 ");
        if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
        if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),UpdateDate,111) = convert(VARCHAR(30),Convert(datetime, '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" and s.ID not   in   
    (select   top " + start +
                   @" s.ID from   System_Mark s inner join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where 1=1 ";
        if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
        if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),UpdateDate,111) = convert(VARCHAR(30),Convert(datetime, '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql +=@" order by UpdateDate desc) ";
            sql = sql + " order by UpdateDate desc ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(System_MarkManageModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddSystem_Mark(string ZiJi, string OldContent, string NewContent, int EmpID, string CaoZuoFangShi, string ZiDuan, string DengLuRen)
        {
            string sql = string.Format(@"INSERT INTO System_Mark
           ([UpdateDate]
           ,[InvolvingSubset]
           ,[Content]
           ,[InvolvingPersonID]
           ,[Operator],[OPerationStyle]
      ,[InvolvingColumn],OldContent)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
,'{5}'
           ,'{6}','{7}')", DateTime.Now, ZiJi, NewContent, EmpID, DengLuRen, CaoZuoFangShi, ZiDuan, OldContent);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateSystem_Mark(System_MarkManageModel item)
        {
            string sql = string.Format(@"UPDATE System_Mark
   SET [UpdateDate] = '{0}'
      ,[InvolvingSubset] =  '{1}'
      ,[Content] =  '{2}'
      ,[InvolvingPersonID] =  '{3}'
      ,[Operator] =  '{4}',[OPerationStyle] = '{5}'
      ,[InvolvingColumn] = '{6}',OldContent = '{7}'
 WHERE ID = {8}", item.UpdateDate, item.InvolvingSubset, item.Content, item.InvolvingPersonID, item.Operator, item.OPerationStyle, item.InvolvingColumn, item.OldContent, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 车改登记
        public int GetCheGaiCounts(string caozuoren, string caozuoriqi, string endcaozuoriqi, string orgid, string status, int keyid,string dengjibiandong)
        {
            string sql = "";
            //用于查看员工列表
//            if (GetDengLuJueSe(dengluren) != "管理员")
//                sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
//              FROM CheGai emp  where Operator = '{0}' and 1=1", dengluren);
//            else
            sql = string.Format(@"SELECT case when count(cg.[ID])%20 != 0 then  count(cg.[ID])/20 + 1 else count(cg.[ID])/20 end Count
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.EmpId = emp.id
  inner join orgforhr o on o.id = emp.orgid where cg.type = '{0}' and 1=1 ",dengjibiandong);
            //if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (keyid != 0) sql += " and cg.keyid = " + keyid;
            if (!string.IsNullOrEmpty(status) && status != "全部") sql += " and cg.[Status] = '" + status + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }
        public void InsertCheGai(int EmpID,  string Type,string dengjibiandongtype)
        {
            string sql =
                string.Format(
                    @"INSERT INTO CheGai
           ([EmpID]
           ,[OldZhiJi]
           ,[Status]
           ,[Demo]
           ,[RelationID],keyid,type,CreateDate)
     select {0},ed.dutylevel,'未导出','{1}',{0},
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='{2}' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='{2}' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'{2}',getdate()
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = {0}",
                    EmpID, Type, dengjibiandongtype);
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }
        public ObservableCollection<CheGaiModel> GetCheGai(string caozuoren, string caozuoriqi, string endcaozuoriqi, string orgid, string status, int start, int end, int keyid, string dengjibiandong)
        {
            ObservableCollection<CheGaiModel> rights = new ObservableCollection<CheGaiModel>();
            string sql = 
                string.Format(@"SELECT top " + (end - start) +
                              @" cg.[ID],'false' as IsSelected
      ,[EmpID]
      ,[OldZhiJi]
      ,[OldJinE]
      ,case when [ChangeDate] = '1900-01-01 00:00:00.000' then null else ChangeDate end ChangeDate
      ,[NewZhiJi]
      ,[NewJinE]
      ,[Operator]
      ,[CreateDate]
      ,cg.[Status]
      ,cg.[Demo]
      ,[RelationID],emp.name EmpName,cg.type,cg.keyid,o.type OrgType,cg.Gender,cg.NowDuty
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.empid = emp.id inner join orgforhr o on o.id = emp.orgid where cg.type = '{0}' and 1=1 ", dengjibiandong);
            //if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (keyid != 0) sql += " and cg.keyid = " + keyid;
            if (!string.IsNullOrEmpty(status) && status != "全部") sql += " and cg.[Status] = '" + status + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" and cg.ID not   in   
    (select   top " + start +
                   @" s.ID from   CheGai s inner join Emp_BasicInfo emp on s.EmpID = emp.ID inner join orgforhr o on o.id = emp.orgid  where 1=1 ";
            //if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (keyid != 0) sql += " and cg.keyid = " + keyid;
            if (!string.IsNullOrEmpty(status) && status != "全部") sql += " and cg.[Status] = '" + status + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" order by keyid, CreateDate desc) ";
            sql = sql + " order by keyid, CreateDate desc ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CheGaiModel.FillEntity(dr));
            }
            return rights;
        }

        public CheGaiModel GetOneCheGai(int id)
        {
            ObservableCollection<CheGaiModel> rights = new ObservableCollection<CheGaiModel>();
            string sql =
                string.Format(@"SELECT cg.[ID],'false' as IsSelected
      ,[EmpID]
      ,[OldZhiJi]
      ,case when oldjine is null then zj.jine else oldjine end as [OldJinE]
      ,case when [ChangeDate] = '1900-01-01 00:00:00.000' then null else ChangeDate end ChangeDate
      ,ed.dutylevel as [NewZhiJi]
      ,case when newjine is null then zj1.jine else newjine end as [NewJinE]
      ,[Operator]
      ,[CreateDate]
      ,cg.[Status]
      ,cg.[Demo]
      ,[RelationID],emp.name EmpName,cg.type,cg.keyid,o.type OrgType,emp.Gender,ed.DutyName as NowDuty
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.empid = emp.id 
  inner join orgforhr o on o.id = emp.orgid
  left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任' 
  left join zhiji zj on zj.dutylevel = oldzhiji
  left join zhiji zj1 on zj1.dutylevel = ed.dutylevel where cg.id = {0} ", id);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CheGaiModel.FillEntity(dr));
            }
            if (rights.Count > 0)
                return rights[0];
            else return null;
        }

        public bool UpdateCheGai(CheGaiModel item)
        {
            string sql = string.Format(@"UPDATE CheGai
   SET [EmpID] = {0}
      ,[OldZhiJi] = '{1}'
      ,[OldJinE] = '{2}'
      ,[ChangeDate] = '{3}'
      ,[NewZhiJi] = '{4}'
      ,[NewJinE] = '{5}'
      ,[Operator] = '{6}'
      ,[CreateDate] = '{7}'
      ,[Status] = '{8}'
      ,[Demo] = '{9}'
      ,[RelationID] = {10},type = '{11}',keyid={12},gender = '{13}',nowduty = '{14}'
 WHERE ID = {15}", item.EmpID, item.OldZhiJi, item.OldJinE, item.ChangeDate, item.NewZhiJi, item.NewJinE, item.Operator,
                  item.CreateDate, item.Status, item.Demo, item.RelationID, item.Type,item.KeyID,item.Gender,item.NowDuty,
                 item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateCheGaiAll(ObservableCollection<CheGaiModel> item)
        {
            string ids = "";
            foreach(CheGaiModel i in item)
            {
                ids += i.ID.ToString() + ",";
            }
            ids = ids.Substring(0, ids.Length - 1);
            string sql = string.Format(@"UPDATE CheGai
   SET [Status] = '已导出'
 WHERE ID in {0}",  "(" + ids + ")");
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public ObservableCollection<string> GetKeyIDs(string caozuoriqi, string endcaozuoriqi, string orgid)
        {
            ObservableCollection<string> rights = new ObservableCollection<string>();
            string sql = string.Format(@"SELECT distinct keyid
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.EmpId = emp.id
  inner join orgforhr o on o.id = emp.orgid where 1=1  ");
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(dr[0].ToString());
            }
            return rights;
        }
        //private byte[] _content = null;
        //public int _id;
        //public FileTransferStream GetExcelFile(ObservableCollection<CheGaiModel> models)
        //{
        //    FileTransferStream ftfs = new FileTransferStream();
        //    if (_content != null)
        //    {
        //    }
        //    else
        //    {
        //        HSSFWorkbook workbook = null;


        //        MemoryStream ms = new MemoryStream(ResourceFile.mianshi);
        //        workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
        //        ms.Dispose();
        //        GetInfo(models, workbook);

        //        string strFileName = AppDomain.CurrentDomain.BaseDirectory +  ".xls";
        //        if (File.Exists(strFileName))
        //        {
        //            File.Delete(strFileName);
        //        }
        //        using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
        //        {
        //            workbook.Write(fs);

        //        }
        //        using (FileStream file = File.Open(strFileName, FileMode.Open))
        //        {
        //            BinaryReader r = new BinaryReader(file);

        //            r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

        //            _content = r.ReadBytes((int)r.BaseStream.Length);
        //        }
        //        if (File.Exists(strFileName))
        //        {
        //            File.Delete(strFileName);
        //        }

        //        ftfs.IsFinished = true;
        //        ftfs.FileContent = _content;

        //    }
        //    return ftfs;
        //}
        //private DataSet GetInfo(ObservableCollection<CheGaiModel> models, HSSFWorkbook workbook)
        //{
        //    HSSFSheet sheet1 = workbook.GetSheetAt(0);
        //    HSSFCellStyle style = workbook.CreateCellStyle();
        //    style.Alignment = HSSFCellStyle.ALIGN_CENTER;
        //    style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
        //    style.BorderBottom = HSSFCellStyle.BORDER_THIN;
        //    style.BorderLeft = HSSFCellStyle.BORDER_THIN;
        //    style.BorderRight = HSSFCellStyle.BORDER_THIN;
        //    style.BorderTop = HSSFCellStyle.BORDER_THIN;
        //    #region 基本信息

        //    for (int i = 0; i < models.Count; i++)
        //    {
        //        var row = sheet1.CreateRow(i + 3);
        //        row.Height = 20 * 20;
        //        for (int ii = 0; ii < 31; ii++)
        //        {
        //            HSSFCell cell = row.CreateCell(ii);
        //            cell.CellStyle = style;
        //        }
        //        SetCellValue(sheet1, i + 7, 0, (i + 1).ToString());
        //        SetCellValue(sheet1, i + 7, 1, models[i].RiQi != null ? Convert.ToDateTime(models[i].RiQi).ToString("yyyy-MM-dd") : null);
        //        SetCellValue(sheet1, i + 7, 2, models[i].Zu);
        //        SetCellValue(sheet1, i + 7, 3, models[i].Shi);
        //        SetCellValue(sheet1, i + 7, 4, models[i].Department);
        //        SetCellValue(sheet1, i + 7, 5, models[i].Duty);
        //        SetCellValue(sheet1, i + 7, 6, models[i].EmpName);
        //        SetCellValue(sheet1, i + 7, 7, models[i].BiShiScore.ToString());
        //        SetCellValue(sheet1, i + 7, 8, models[i].CardNo);
        //        SetCellValue(sheet1, i + 7, 9, models[i].OrgName);
        //    }
        //    #endregion
            
        //    return null;
        //}
        //private bool SetCellValue(HSSFCell cell, object value)
        //{
        //    if (value != null)
        //    {
        //        cell.SetCellValue(value.ToString());
        //    }
        //    return true;
        //}
        //private bool SetCellValue(HSSFSheet sheet, int row, int column, string dr)
        //{
        //    HSSFRow hrow = sheet.GetRow(row - 1);
        //    if (hrow != null)
        //    {
        //        HSSFCell cell = hrow.GetCell(column);
        //        if (cell != null)
        //        {
        //            SetCellValue(cell, dr);
        //        }
        //        else
        //        {
        //            return false;
        //        }
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //    return true;
        //}
        #endregion

        #region 组织机构
        public ObservableCollection<OrgForHRModel> GetOrgForHRModel(int OrgID)
        {
            ObservableCollection<OrgForHRModel> rights = new ObservableCollection<OrgForHRModel>();
            string sql = "";
            if (OrgID == 0)
                sql = string.Format(@"SELECT [ID]
      ,[Name]
      ,[Type]
      ,[ParentID]
      ,[OrgNo]
  FROM OrgForHR");
            else
                sql = string.Format(@"SELECT [ID]
      ,[Name]
      ,[Type]
      ,[ParentID]
      ,[OrgNo]
  FROM OrgForHR where ID = {0}", OrgID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(OrgForHRModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<OrgForHRModel> GetOrgForHRModelbyIDs(string OrgID)
        {
            ObservableCollection<OrgForHRModel> rights = new ObservableCollection<OrgForHRModel>();
            string
                sql = @"SELECT [ID]
      ,[Name]
      ,[Type]
      ,[ParentID]
      ,[OrgNo]
  FROM OrgForHR where ID in " + OrgID;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(OrgForHRModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddOrgForHRModel(OrgForHRModel item)
        {
            string sql = string.Format(@"INSERT INTO OrgForHR
           ([Name]
           ,[Type]
           ,[ParentID]
           ,[OrgNo])
     VALUES
           ('{0}'
           ,'{1}'
           ,(select ID from OrgForHR where Name = '{1}')
           ,'{2}')", item.Name, item.Type, item.OrgNo);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateOrgForHRModel(OrgForHRModel item)
        {
            string sql = string.Format(@"UPDATE OrgForHR
   SET [Name] = '{0}'
      ,[Type] = '{1}'
      ,[ParentID] = '{2}'
      ,[OrgNo] = '{3}'
 WHERE ID = {4}", item.Name, item.Type, item.ParentID, item.OrgNo, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeteleOrgForHRModel(int ID)
        {
            string sql = string.Format(@"
delete from OrgForHR where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 编制管理
        public ObservableCollection<BianZhiModel> GetBianZhiModel(int OrgID, int ID)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT o.Name OrgName, bi.[ID]
      ,[OrgID]
      ,[ZongBianZhi]
      ,[ZhengKeLingDao]
      ,[ZhengKeFeiLingDao]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao]
      ,[KeYuan]
      ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu
  FROM BianZhi bi right join OrgForHR o on bi.orgid = o.ID where OrgID = {0}", OrgID);
            else
                sql = string.Format(@"SELECT o.Name OrgName, bi.[ID]
      ,[OrgID]
      ,[ZongBianZhi]
      ,[ZhengKeLingDao]
      ,[ZhengKeFeiLingDao]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao]
      ,[KeYuan]
      ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu
  FROM BianZhi bi inner join OrgForHR o on bi.orgid = o.ID where o.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddBianZhiModel(BianZhiModel item)
        {
            string sql = string.Format(@"INSERT INTO BianZhi
           ([OrgID]
           ,[ZongBianZhi]
           ,[ZhengKeLingDao]
           ,[ZhengKeFeiLingDao]
           ,[FuKeLingDao]
           ,[FuKeFeiLingDao]
           ,[KeYuan]
           ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}','{8}'
           ,'{9}')", item.OrgID, item.ZongBianZhi, item.ZhengKeLingDao, item.ZhengKeFeiLingDao
                   , item.FuKeLingDao, item.FuKeFeiLingDao, item.KeYuan, item.WeiDingZhi, item.ChuJiRenShu, item.KeYiXiaRenShu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateBianZhiModel(BianZhiModel item)
        {
            string sql = string.Format(@"UPDATE BianZhi
   SET [OrgID] = {0}
      ,[ZongBianZhi] = '{1}'
      ,[ZhengKeLingDao] = '{2}'
      ,[ZhengKeFeiLingDao] = '{3}'
      ,[FuKeLingDao] = '{4}'
      ,[FuKeFeiLingDao] = '{5}'
      ,[KeYuan] = '{6}'
      ,[WeiDingZhi] = '{7}',[ChuJiRenShu] = '{8}'
      ,[KeYiXiaRenShu] = '{9}'
 WHERE ID = {10}", item.OrgID, item.ZongBianZhi, item.ZhengKeLingDao, item.ZhengKeFeiLingDao
                   , item.FuKeLingDao, item.FuKeFeiLingDao, item.KeYuan, item.WeiDingZhi, item.ChuJiRenShu, item.KeYiXiaRenShu, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 查询实有人数等
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<BianZhiModel> GetBianZhiModelShiYou(int OrgID)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = string.Format(@"select o.Name OrgName,a.ZongBianShiYou,b.ZhengKeLingDaoShiYou,c.ZhengKeFeiLingDaoShiYou,
d.FuKeLingDaoShiYou,e.FuKeFeiLingDaoShiYou, f.KeYuanShiYou,g.WeiDingZhiShiYou,b1.ZongBianZhi,b1.ChuJiRenShu from OrgForHR o left join
(select case when COUNT(distinct ID) is null then 0 else COUNT(distinct ID) end ZongBianShiYou ,orgid OrgID from Emp_BasicInfo where empstatus = '在职' and orgID = {0} group by orgid) a on o.ID = a.OrgID left join
(select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end ZhengKeLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' group by orgid) b on b.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end ZhengKeFeiLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' group by orgid) c on c.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end FuKeLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' group by orgid) d on d.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) = null then 0 else COUNT(distinct EmpbasicID) end FuKeFeiLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '非领导职务' group by orgid) e on e.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end KeYuanShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '科员'  group by orgid) f on f.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end WeiDingZhiShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '未定职'  group by orgid) g on g.OrgID = o.ID   left join 
 (select * from BianZhi where OrgID = {0}) b1 on b1.OrgID = o.ID where o.ID = {0}", OrgID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity1(dr));
            }
            return rights;
        }
        #endregion

        #region 职务层次
        public ObservableCollection<ZhiJiModel> GetZhiJiModel( int ID)
        {
            ObservableCollection<ZhiJiModel> rights = new ObservableCollection<ZhiJiModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select * from zhiji");
            else
                sql = string.Format(@"select * from zhiji where ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhiJiModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddZhiJiModel(ZhiJiModel item)
        {
            string sql = string.Format(@"INSERT INTO ZhiJi
           ([DutyLevel]
           ,[JinE])
     VALUES
           ('{0}'
           ,'{1}')", item.DutyLevel,item.JinE);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateZhiJiModel(ZhiJiModel item)
        {
            string sql = string.Format(@"UPDATE ZhiJi
   SET [DutyLevel] = '{0}'
      ,[JinE] = '{1}'
 WHERE ID = {2}", item.DutyLevel,item.JinE, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        /// <summary>
        /// 导入数据前判断是否存在错误数据
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <returns></returns>
        public bool CanDaoRuAll(ObservableCollection<Emp_BasicInfoModel> InEmpBasicModels)
        {
            if (InEmpBasicModels != null && InEmpBasicModels.Count > 0)
            {
                foreach (Emp_BasicInfoModel i in InEmpBasicModels)
                {
                    string sql = string.Format(@"select * from Emp_BasicInfo where Name = '{0}' and CardNo = '{1}'",
                                               i.Name, i.CardNo);
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        return false;
                    }
                }
                return true;
            }
            else return false;
        }
        /// <summary>
        /// 删除在导入过程中数据库中已经存在的重复数据
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <returns></returns>
        public bool ShanChuDaoRuAll(ObservableCollection<Emp_BasicInfoModel> InEmpBasicModels)
        {
            int can = 0;
            if (InEmpBasicModels != null && InEmpBasicModels.Count > 0)
            {
                foreach (Emp_BasicInfoModel i in InEmpBasicModels)
                {
                    string sql = string.Format(@"delete from Emp_BasicInfo where Name = '{0}' and CardNo = '{1}'",
                                               i.Name, i.CardNo);
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        can = can;
                    else can = can + 1;
                }
                if (can == InEmpBasicModels.Count) return true;
                else return false;
            }
            else return false;
        }
        /// <summary>
        /// 导入数据(数据表导出和单位数据表导出)
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <param name="InEmpEducation"></param>
        /// <param name="InEmpDegree"></param>
        /// <param name="InEmpResume"></param>
        /// <param name="InEmpDuty"></param>
        /// <param name="InEmpReward"></param>
        /// <param name="InEmpPunish"></param>
        /// <param name="InEmpAssenssment"></param>
        /// <param name="InEmpFamily"></param>
        public bool DaoRuAll(ObservableCollection<Emp_BasicInfoModel> InEmpBasicModels, ObservableCollection<Emp_EducationModel> InEmpEducation,
            ObservableCollection<Emp_DegreeModel> InEmpDegree, ObservableCollection<Emp_ResumeModel> InEmpResume,
            ObservableCollection<Emp_Duty> InEmpDuty, ObservableCollection<Emp_RewardInfoModel> InEmpReward,
            ObservableCollection<Emp_PunishmentModel> InEmpPunish, ObservableCollection<Emp_AssessmentModel> InEmpAssenssment,
            ObservableCollection<Emp_FamilyRelationModel> InEmpFamily)
        {
            if (InEmpBasicModels != null && InEmpBasicModels.Count > 0)
            {
                foreach (Emp_BasicInfoModel i in InEmpBasicModels)
                {
                    string sql = string.Format(@"INSERT INTO Emp_BasicInfo
           ([Name]
           ,[CardNo]
           ,[Gender]
           ,[BirthPlace]
           ,[HouseholdRegister]
           ,[Native]
           ,[Political]
           ,[Health]
           ,[WorkDate]
           ,[RegisterNo]
           ,[OrganizationType]
           ,[NowWorkDate]
           ,[NativePlace]
           ,[Marriage]
           ,[HomeAddress]
           ,[Phone]
           ,[EmpIdentity]
           ,[PartyTime]
           ,[Special]
           ,[TechnicalPositions]
           ,[PersonnelType]
           ,[RegisterDate]
            ,[Birthday]
,OrgID
        ,PositionName,TuiXiuDate,JuTuiXiuDate,HuoDeFangShi,EmpStatus)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}'
           ,'{20}'
           ,'{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}');SELECT @@IDENTITY ", i.Name, i.CardNo, i.Gender, i.BirthPlace, i.HouseholdRegister, i.Native, i.Political, i.Health,
                    i.WorkDate, i.RegisterNo, i.OrganizationType, i.NowWorkDate, i.NativePlace, i.Marriage, i.HomeAddress,
                    i.Phone, i.EmpIdentity, i.PartyTime, i.Special, i.TechnicalPositions, i.PersonnelType, i.RegisterDate, i.Birthday,
                    i.OrgID, i.PositionName, i.TuiXiuDate, i.JuTuiXiuDate, i.HuoDeFangShi, "在职");
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    int EmpID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    #region 学历
                    if (InEmpEducation != null && InEmpEducation.Count > 0)
                    {
                        List<Emp_EducationModel> tem =
                            InEmpEducation.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_EducationModel edu in tem)
                            {
                                string edusql = string.Format(@"INSERT INTO Emp_Education
           ([EmpBasicID]
           ,[EducationName]
           ,[EducationType]
           ,[EducationaSystem]
           ,[GraduationDate]
           ,[School]
           ,[Professional]
           ,[IsTopEducation])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}')", EmpID, edu.EducationName, edu.EducationType, edu.EducationaSystem, edu.GraduationDate, edu.School, edu.Professional, edu.IsTopEducation);
                                AccessHelper.GetInstance().ExcuteDataSet(edusql);
                            }
                        }
                    }
                    #endregion
                    #region 学位
                    if (InEmpDegree != null && InEmpDegree.Count > 0)
                    {
                        List<Emp_DegreeModel> tem =
                            InEmpDegree.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_DegreeModel edu in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Degree
           ([EmpbasicID]
           ,[DegreeName]
           ,[LearningType]
           ,[EducationalSystem]
           ,[EducationDate]
           ,[School]
           ,[Professional])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, edu.DegreeName, edu.LearningType, edu.EducationalSystem, edu.EducationDate, edu.School, edu.Professional);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 简历
                    if (InEmpResume != null && InEmpResume.Count > 0)
                    {
                        List<Emp_ResumeModel> tem =
                            InEmpResume.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_ResumeModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Resume
           ([EmpbasicID]
           ,[BeginDate]
           ,[EndDate]
           ,[Unit]
           ,[Duty]
           ,[Witness]
           ,[IsPeriod])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.BeginDate, item.EndDate, item.Unit, item.Duty, item.Witness, item.IsPeriod);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 职务
                    if (InEmpDuty != null && InEmpDuty.Count > 0)
                    {
                        List<Emp_Duty> tem =
                            InEmpDuty.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_Duty item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[DutyLevel]
           ,[DutyAttribute]
           ,[OKDate]
           ,[OrganizationName]
           ,[Sec]
           ,[WorkCharge]
           ,[ServiceMode]
           ,[IsExceptionalPromotion]
           ,[JobStatus]
           ,[JobDate],RenQi,RZSYQ,LianRen,BRZDQRQ,RXRZWSXSJ)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}','{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}')", EmpID, item.DutyNo, item.DutyName, item.DutyLevel, item.DutyAttribute, item.OKDate, item.OrganizationName, item.Sec,
                    item.WorkCharge, item.ServiceMode, item.IsExceptionalPromotion, item.JobStatus, item.JobDate
                    , item.RenQi, item.RZSYQ, item.LianRen, item.BRZDQRQ, item.RXRZWSXSJ);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 奖励
                    if (InEmpReward != null && InEmpReward.Count > 0)
                    {
                        List<Emp_RewardInfoModel> tem =
                            InEmpReward.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_RewardInfoModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_RewardInfo
           ([EmpbasicID]
           ,[RewardNo]
           ,[RewardName]
           ,[RewardReason]
           ,[OtherRewardReason]
           ,[OKDate]
           ,[OKUnit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.RewardNo, item.RewardName, item.RewardReason, item.OtherRewardReason, item.OKDate, item.OKUnit);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 处分
                    if (InEmpPunish != null && InEmpPunish.Count > 0)
                    {
                        List<Emp_PunishmentModel> tem =
                            InEmpPunish.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_PunishmentModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Punishment
           ([EmpbasicID]
           ,[PunishmentNo]
           ,[PunishmentName]
           ,[PunishmentReason]
           ,[OkUnit]
           ,[OKDate]
           ,[PunishmentTimeLimit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.PunishmentNo, item.PunishmentName, item.PunishmentReason, item.OkUnit, item.OKDate, item.PunishmentTimeLimit);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 考核
                    if (InEmpAssenssment != null && InEmpAssenssment.Count > 0)
                    {
                        List<Emp_AssessmentModel> tem =
                            InEmpAssenssment.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_AssessmentModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Assessment
           ([EmpbasicID]
           ,[AssessmentType]
           ,[AssessmentResult]
           ,[AssessmentDate])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}')", EmpID, item.AssessmentType, item.AssessmentResult, item.AssessmentDate);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 家庭
                    if (InEmpFamily != null && InEmpFamily.Count > 0)
                    {
                        List<Emp_FamilyRelationModel> tem =
                            InEmpFamily.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_FamilyRelationModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_FamilyRelation
           ([EmpbasicID]
           ,[Name]
           ,[Appellation]
           ,[Unit]
           ,[Duty]
           ,[Political]
           ,[Birthday])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.Name, item.Appellation, item.Unit, item.Duty, item.Political, item.Birthday);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                }
            }
            return true;
        }

        public PeiZhiLieModel GetLieByUserID(int userID)
        {
            ObservableCollection<PeiZhiLieModel> rights = new ObservableCollection<PeiZhiLieModel>();
            string sql = string.Format(@"select * from peizhilie where userid  = {0}", userID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PeiZhiLieModel.FillEntity(dr));
            }
            if (rights.Count > 0)
                return rights[0];
            else return null;
        }

        public bool UpdateLie(PeiZhiLieModel item)
        {
            string sql = "";
            if (item.ID == 0)
            {
                sql = string.Format(@"INSERT INTO PeiZhiLie
           ([UserID]
           ,[Content])
     VALUES
           ({0}
           ,'{1}')", item.UserID, item.Content);
            }
            else
            {
                sql = string.Format(@"UPDATE PeiZhiLie
   SET [UserID] = {0}
      ,[Content] = '{1}'
 WHERE ID = {2}", item.UserID, item.Content, item.ID);
            }

            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
    }
}
